package com.haitao.wavebbs.dao;

import com.haitao.wavebbs.entity.Post;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface PostRepository extends CrudRepository<Post,Integer> {

    @Query(value = "select * from post order by updated_time desc limit ?1 offset ?2",nativeQuery=true)
    List<Post> getPosts(int per_page,int offset);

    @Query(value = "select * from post order by updated_time desc limit ?1 offset ?2  where userId= ?3",nativeQuery=true)
    List<Post> getPostsByUserId(int per_page,int offset,int userId);

    /**
     * 后一页（updatedTime之前的时间）
     * @param per_page
     * @param updatedTime
     * @param lastOrNext
     * @return
     */
    @Query(value = "select * from post where updated_time < ?2 order by updated_time desc   limit ?1*?3,?1",nativeQuery=true)
    List<Post> getPostsNextPage(int per_page,String updatedTime,int lastOrNext);

    /**
     * 前一页(updatedTime之后的时间)
     * @param per_page
     * @param updatedTime
     * @param lastOrNext
     * @return
     */
    @Query(value = "select * from post where updated_time > ?2 order by updated_time  limit ?1*?3,?1",nativeQuery=true)
    List<Post> getPostsPreviousPage(int per_page,String updatedTime,int lastOrNext);

    /**
     * 某一个用户的后一页
     * @param per_page
     * @param updatedTime
     * @param lastOrNext
     * @param userId
     * @return
     */
    @Query(value = "select * from post where updated_time < ?2 and userId = ?4 order by updated_time desc limit ?1*?3,?1",nativeQuery=true)
    List<Post> getPostsByUserIdNextPage(int per_page,String updatedTime,int lastOrNext,int userId);

    /**
     * 某一个用户的前一页
     * @param per_page
     * @param updatedTime
     * @param lastOrNext
     * @param userId
     * @return
     */
    @Query(value = "select * from post where updated_time > ?2 and userId = ?4 order by updated_time  limit ?1*?3,?1",nativeQuery=true)
    List<Post> getPostsByUserIdPreviousPage(int per_page,String updatedTime,int lastOrNext,int userId);


    @Query(value = "select * from post  where PostId= ?1",nativeQuery=true)
    Post getPostByPostId(int postId);

    @Query(value = "select count(*) from post",nativeQuery=true)
    int getPostAmount();
}
